﻿using System;

using System.Collections;

using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using Global.Class.Library;
using BusinessLogicLayer;
using BusinessLogicLayer.MetaDataList;
using DataAccessLayer;
using DataTransferObject;
using DataTransferObject.MetaDataList;

using System.Globalization;
using BrightIdeasSoftware;
using Equin.ApplicationFramework;


using OfficeExcel = Microsoft.Office.Interop.Excel;


namespace PresentationLayer
{
    public partial class Quota : Form, IMergeToolStrip, ICallToolStrip
    {

        #region <Implement Interface>

        public event PropertyChangedEventHandler PropertyChanged;

        private void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null) PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }

        public GlobalEnum.TaskID TaskID
        {
            get { return this.quotaBLL.TaskID; }
        }


        public ToolStrip ChildToolStrip
        {
            get
            {
                return this.toolStripChildForm;
            }
            set
            {
                this.toolStripChildForm = value;
            }
        }





        #region Context toolbar


        public bool IsDirty
        {
            get { return this.quotaBLL.IsDirty; }
        }

        public bool IsValid
        {
            get { return this.quotaBLL.IsValid; }
        }

        public bool Closable
        {
            get { return true; }
        }

        public bool Loadable
        {
            get { return true; }
        }

        public bool Newable
        {
            get { return !this.quotaBLL.ReadOnly; }
        }

        public bool Editable
        {
            get { return this.quotaBLL.Editable; }
        }

        public bool Deletable
        {
            get { return this.quotaBLL.Editable; }
        }

        public bool Importable
        {
            get { return true; }
        }

        public bool Exportable
        {
            get { return true; }
        }

        public bool Verifiable
        {
            get { return this.quotaBLL.Verifiable; }
        }

        public bool Unverifiable
        {
            get { return this.quotaBLL.Unverifiable; }
        }

        public bool Printable
        {
            get { return false; }
        }

        public bool Searchable
        {
            get { return true; }
        }


        /// <summary>
        /// Edit Mode: True, Read Mode: False
        /// </summary>
        private bool editableMode;
        private int lastQuotaID;
        public bool EditableMode { get { return this.editableMode; } }
        /// <summary>
        /// This reverse of the EditableMode
        /// </summary>
        public bool ReadonlyMode { get { return !this.editableMode; } }

        /// <summary>
        /// Set the editableMode
        /// </summary>
        /// <param name="editableMode"></param>
        private void SetEditableMode(bool editableMode)
        {
            if (this.editableMode != editableMode)
            {
                this.lastQuotaID = this.quotaBLL.QuotaID;
                this.editableMode = editableMode;
                this.NotifyPropertyChanged("EditableMode");

                this.toolStripMenuCustomerImport.Enabled = this.editableMode;
                this.naviGroupDetails.Expanded = editableMode;
            }
        }


        private void CancelDirty(bool restoreSavedData)
        {
            try
            {
                if (restoreSavedData || this.quotaBLL.QuotaID <= 0)
                    this.quotaBLL.Fill(this.lastQuotaID);

                this.SetEditableMode(false);
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }



        #endregion Context toolbar



        #region ICallToolStrip

        public void Escape()
        {
            if (this.EditableMode)
            {
                if (this.IsDirty)
                {
                    DialogResult dialogResult = MessageBox.Show(this, "Do you want to save your change?", "Warning", MessageBoxButtons.YesNoCancel);
                    if (dialogResult == DialogResult.Yes)
                    {
                        this.Save();
                        if (!this.IsDirty) this.CancelDirty(false);
                    }
                    else if (dialogResult == DialogResult.No)
                    {
                        this.CancelDirty(true);
                    }
                    else
                        return;
                }
                else
                    this.CancelDirty(false);
            }
            else
                this.Close(); //Unload this module
        }

        //Can phai xem lai trong VB de xem lai this.SetEditableMode () khi can thiet

        public void Loading()
        {
            this.GetMasterList();
        }

        public void New()
        {
            this.SetEditableMode(true);
            this.quotaBLL.New();
        }

        public void Edit()
        {
            this.SetEditableMode(true);
            this.quotaBLL.Edit();
        }

        public void Save()
        {
            this.quotaBLL.Save();
            this.GetMasterList();
        }

        public void Delete()
        {
            DialogResult dialogResult = MessageBox.Show(this, "Are you sure you want to delete " + this.quotaBLL.QuotaMaster.Reference + "?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Stop);
            if (dialogResult == DialogResult.Yes)
            {
                try
                {
                    this.quotaBLL.Delete();
                    this.GetMasterList();
                }
                catch (Exception exception)
                {
                    GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
                }
            }
        }

        public void Import()
        {
            ImportExcel();
        }

        public void Export()
        {
            try
            {
                if (this.ActiveControl.Equals(this.dataListViewMaster))
                {
                    DataTable dataTableExport;
                    dataTableExport = this.dataListViewMaster.DataSource as DataTable;
                    if (dataTableExport != null) CommonFormAction.Export(dataTableExport);
                }
                else
                {
                    List<QuotaDetail> listExport;
                    listExport = this.quotaBLL.QuotaDetailList.ToList();
                    if (listExport != null) CommonFormAction.Export(listExport);
                }
            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
        }

        public void Verify()
        {
            MessageBox.Show("Verify");
        }

        public void Print(PrintDestination printDestination)
        {
            MessageBox.Show("Print");
        }

        public void SearchText(string searchText)
        {
            CommonFormAction.OLVFilter(this.dataListViewMaster, searchText);
        }

        #endregion


        #endregion <Implement Interface>









        #region <Storage>

        private const int colQuotaAmount = 7;


        private QuotaBLL quotaBLL;

        #endregion <Storage>


        public Quota()
        {
            try
            {
                InitializeComponent();


                this.quotaBLL = new QuotaBLL();

                this.quotaBLL.PropertyChanged += new PropertyChangedEventHandler(quotaBLL_PropertyChanged);

            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
        }

        void quotaBLL_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            this.NotifyPropertyChanged(e.PropertyName);
        }


        private void Quota_Load(object sender, EventArgs e)
        {
            try
            {
                InitializeTabControl();

                InitializeCommonControlBinding();

                InitializeDataGridBinding();

                InitializeReadOnlyModeBinding();
            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
        }


        private void InitializeTabControl()
        {
            try
            {
                CustomTabControl customTabControlQuotaDetail = new CustomTabControl();

                customTabControlQuotaDetail.ImageList = this.imageListTabControl;


                customTabControlQuotaDetail.TabPages.Add("QuotaDetail", "Quota    ", 6);
                customTabControlQuotaDetail.TabPages[0].Controls.Add(this.dataGridViewQuotaDetail);
                this.dataGridViewQuotaDetail.Dock = DockStyle.Fill;

                customTabControlQuotaDetail.DisplayStyle = TabStyle.VisualStudio;
                customTabControlQuotaDetail.DisplayStyleProvider.ImageAlign = ContentAlignment.MiddleLeft;
                this.naviGroupDetails.Controls.Add(customTabControlQuotaDetail);
                this.naviGroupDetails.Controls.SetChildIndex(customTabControlQuotaDetail, 0);
                customTabControlQuotaDetail.Dock = DockStyle.Fill;


            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
        }


        #region <InitializeBinding>
        

        Binding salesmenIDBinding;
        Binding customerTypeIDBinding;
        Binding customerCategoryIDBinding;
        Binding customerChannelIDBinding;

        Binding quotaDateBinding;
        Binding entryDateBinding;

        Binding isDirtyBinding;
        Binding isDirtyBLLBinding;

        private void InitializeCommonControlBinding()
        {
            CommonMetaList commonMetaList = new CommonMetaList();

            ERmgrUIP.ListSalesmenNameDataTable listSalesmenNameDataTable = commonMetaList.GetSalesmenName();
            this.comboBoxSalesmenID.DataSource = listSalesmenNameDataTable;
            this.comboBoxSalesmenID.DisplayMember = listSalesmenNameDataTable.DescriptionOfficialColumn.ColumnName;
            this.comboBoxSalesmenID.ValueMember = listSalesmenNameDataTable.SalesmenIDColumn.ColumnName;
            this.salesmenIDBinding = this.comboBoxSalesmenID.DataBindings.Add("SelectedValue", this.quotaBLL.QuotaMaster, "SalesmenID", true);

            ERmgrUIP.ListCustomerTypeDataTable listCustomerTypeDataTable = commonMetaList.GetCustomerType();
            this.comboBoxCustomerTypeID.DataSource = listCustomerTypeDataTable;
            this.comboBoxCustomerTypeID.DisplayMember = listCustomerTypeDataTable.DescriptionColumn.ColumnName;
            this.comboBoxCustomerTypeID.ValueMember = listCustomerTypeDataTable.CustomerTypeIDColumn.ColumnName;
            this.customerTypeIDBinding = this.comboBoxCustomerTypeID.DataBindings.Add("SelectedValue", this.quotaBLL, "CustomerTypeID", true);

            ERmgrUIP.ListCustomerCategoryDataTable listCustomerCategoryDataTable = commonMetaList.GetCustomerCategory();
            this.comboBoxCustomerCategoryID.DataSource = listCustomerCategoryDataTable;
            this.comboBoxCustomerCategoryID.DisplayMember = listCustomerCategoryDataTable.DescriptionColumn.ColumnName;
            this.comboBoxCustomerCategoryID.ValueMember = listCustomerCategoryDataTable.CustomerCategoryIDColumn.ColumnName;
            this.customerCategoryIDBinding = this.comboBoxCustomerCategoryID.DataBindings.Add("SelectedValue", this.quotaBLL, "CustomerCategoryID", true);

            ERmgrUIP.ListCustomerTypeDataTable listCustomerTypeDataTable1 = commonMetaList.GetCustomerType();
            this.comboBoxCustomerChannelID.DataSource = listCustomerTypeDataTable1;
            this.comboBoxCustomerChannelID.DisplayMember = listCustomerTypeDataTable1.DescriptionColumn.ColumnName;
            this.comboBoxCustomerChannelID.ValueMember = listCustomerTypeDataTable1.CustomerTypeIDColumn.ColumnName;
            this.customerChannelIDBinding = this.comboBoxCustomerChannelID.DataBindings.Add("SelectedValue", this.quotaBLL, "CustomerChannelID", true);
      

            this.quotaDateBinding = this.dateTimePickerQuotaDate.DataBindings.Add("Value", this.quotaBLL.QuotaMaster, "QuotaDate", true);
            this.entryDateBinding = this.dateTimePickerEntryDate.DataBindings.Add("Value", this.quotaBLL.QuotaMaster, "EntryDate", true);

            this.isDirtyBinding = this.checkBoxIsDirty.DataBindings.Add("Checked", this.quotaBLL.QuotaMaster, "IsDirty", true);
            this.isDirtyBLLBinding = this.checkBoxIsDirtyBLL.DataBindings.Add("Checked", this.quotaBLL, "IsDirty", true);


            this.salesmenIDBinding.BindingComplete += new BindingCompleteEventHandler(CommonControl_BindingComplete);
            this.customerTypeIDBinding.BindingComplete += new BindingCompleteEventHandler(CommonControl_BindingComplete);
            this.customerCategoryIDBinding.BindingComplete += new BindingCompleteEventHandler(CommonControl_BindingComplete);
            this.customerChannelIDBinding.BindingComplete += new BindingCompleteEventHandler(CommonControl_BindingComplete);
            
            this.quotaDateBinding.BindingComplete += new BindingCompleteEventHandler(CommonControl_BindingComplete);
            this.entryDateBinding.BindingComplete += new BindingCompleteEventHandler(CommonControl_BindingComplete);

            this.isDirtyBinding.BindingComplete += new BindingCompleteEventHandler(CommonControl_BindingComplete);
            this.isDirtyBLLBinding.BindingComplete += new BindingCompleteEventHandler(CommonControl_BindingComplete);


            this.filterTextBox.DataBindings.Add("Visible", this, "EditableMode", true, DataSourceUpdateMode.OnPropertyChanged);
            this.naviGroupDetails.DataBindings.Add("ExpandedHeight", this.numericUpDownSizingDetail, "Value", true, DataSourceUpdateMode.OnPropertyChanged);
            this.numericUpDownSizingDetail.Minimum = this.naviGroupDetails.HeaderHeight * 2;
            this.numericUpDownSizingDetail.Maximum = this.naviGroupDetails.Height + this.dataListViewMaster.Height;

            this.tableLayoutPanelMaster.ColumnStyles[this.tableLayoutPanelMaster.ColumnCount - 1].SizeType = SizeType.Absolute; this.tableLayoutPanelMaster.ColumnStyles[this.tableLayoutPanelMaster.ColumnCount - 1].Width = 10;

            this.errorProviderMaster.DataSource = this.quotaBLL.QuotaMaster;

            DateTime monthID;

            for (int i = 1; i <= 12; i++)
            {
                monthID = new DateTime(1900, i, 1);
                this.comboBoxMonthID.Items.Add(monthID.ToString("MMMM"));
            }

            this.comboBoxMonthID.SelectedIndex = 0;
        }

        private void CommonControl_BindingComplete(object sender, BindingCompleteEventArgs e)
        {
            if (e.BindingCompleteState == BindingCompleteState.Exception) { GlobalExceptionHandler.ShowExceptionMessageBox(this, e.ErrorText); e.Cancel = true; }
        }

        private void numericUpDownSizingDetail_ValueChanged(object sender, EventArgs e)
        {
            this.naviGroupDetails.Expand();
        }

        private void naviGroupDetails_HeaderMouseClick(object sender, MouseEventArgs e)
        {
            this.numericUpDownSizingDetail.Visible = this.naviGroupDetails.Expanded;       
        }   




        private void GetMasterList()
        {
            foreach (OLVColumn olvColumn in dataListViewMaster.Columns) { olvColumn.Renderer = null; }

            DataTable marketingPaymentMasterList = this.quotaBLL.QuotaMasterList(GlobalVariables.GlobalOptionSetting.LowerFillterDate, GlobalVariables.GlobalOptionSetting.UpperFillterDate);
            marketingPaymentMasterList.TableName = "QuotaMasterList";

            DataSet bindingDataSet = new DataSet();
            bindingDataSet.Tables.Add(marketingPaymentMasterList);

            this.dataListViewMaster.AutoGenerateColumns = false;
            this.dataListViewMaster.DataSource = marketingPaymentMasterList;
        }

        private void InitializeDataGridBinding()
        {

            this.GetMasterList();


            #region <dataGridViewDetail>

            CommonMetaList commonMetaList = new CommonMetaList();
   

            this.dataGridViewQuotaDetail.AutoGenerateColumns = false;
            quotaDetailListView = new BindingListView<QuotaDetail>(this.quotaBLL.QuotaDetailList);
            this.dataGridViewQuotaDetail.DataSource = quotaDetailListView;
                       

            StackedHeaderDecorator stackedHeaderDecorator = new StackedHeaderDecorator(this.dataGridViewQuotaDetail);

        
            #endregion <dataGridViewDetail>
        }

        private void InitializeReadOnlyModeBinding()
        {
            List<Control> controlList = GlobalStaticFunction.GetAllControls(this);

            foreach (Control control in controlList)
            {
                if (control.Name != "comboBoxMonthID")
                {
                    //if (control is TextBox) control.DataBindings.Add("Readonly", this, "ReadonlyMode");
                    if (control is TextBox) control.DataBindings.Add("Enabled", this, "EditableMode");
                    else if (control is ComboBox || control is DateTimePicker) control.DataBindings.Add("Enabled", this, "EditableMode");
                    else if (control is DataGridView)
                    {
                        control.DataBindings.Add("Readonly", this, "ReadonlyMode");
                        //control.DataBindings.Add("AllowUserToAddRows", this, "EditableMode");
                        //control.DataBindings.Add("AllowUserToDeleteRows", this, "EditableMode");
                    }
                }
            }

            this.dataListViewMaster.DataBindings.Add("Enabled", this, "ReadonlyMode");
        }

        #endregion <InitializeBinding>




        private void dataListViewMaster_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                ObjectListView objectListView = (ObjectListView)sender;
                DataRowView row = (DataRowView)objectListView.SelectedObject;

                if (row != null)
                {
                    int quotaID;

                    if (int.TryParse(row.Row["QuotaID"].ToString(), out quotaID)) this.quotaBLL.Fill(quotaID);
                    else this.quotaBLL.Fill(-1);
                    
                }
                else this.quotaBLL.Fill(-1);
            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
        }
   

        private void dataGridViewQuotaDetail_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            try
            {
                if (e.ColumnIndex == colQuotaAmount)
                {
                    List<int> listOfSelectedRow = new List<int>();
                    listOfSelectedRow.AddRange(Enumerable.Range(0, this.dataGridViewQuotaDetail.Rows.Count));                    
                }
            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
        }

        private void dataGridViewQuotaDetail_SelectionChanged(object sender, EventArgs e)
        {
            try
            {
                if (this.EditableMode)
                {
                    DataGridView dataGridView = (DataGridView)sender;
                    DataGridViewRow row = dataGridView.CurrentRow;

                    if (row != null)
                    {
                        if ((bool)this.dataGridViewQuotaDetail["IsCommodity", row.Index].Value == false)
                        {
                            this.dataGridViewQuotaDetail.ReadOnly = true;
                        }
                        else
                        {
                            this.dataGridViewQuotaDetail.ReadOnly = false;
                        }

                    }
                }

            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
        }


        BindingListView<QuotaDetail> quotaDetailListView;
        private void filterTextBox_TextChanged(object sender, EventArgs e)
        {// Change the filter of the view.
         


        }

        private void filterTextBox_VisibleChanged(object sender, EventArgs e)
        {
            this.filterTextBox.Clear();
        }

        private void dataGridViewQuotaDetail_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            GlobalExceptionHandler.ShowExceptionMessageBox(this, e.Exception);
        }







        #region Import Excel

        private void toolStripMenuCustomerImport_Click(object sender, EventArgs e)
        {
            this.ImportExcel();
        }


        private void ImportExcel()
        {
            try
            {
                OpenFileDialog openFileDialog = new OpenFileDialog();
                openFileDialog.Filter = "Excel File (.xlsx)|*.xlsx";

                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    DialogMapExcelColumn dialogMapExcelColumn = new DialogMapExcelColumn(OleDbDatabase.MappingTaskID.Quota, openFileDialog.FileName);

                    if (dialogMapExcelColumn.ShowDialog() == DialogResult.OK)
                    {
                        dialogMapExcelColumn.Dispose();
                        if (this.quotaBLL.ImportExcel(openFileDialog.FileName))
                            MessageBox.Show(this, "Congratulation!" + "\r\n" + "\r\n" + "File: " + openFileDialog.FileName + " is imported successfull!" + "\r\n" + "\r\n" + "Please press OK to finish.", "Importing", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        this.GetMasterList();
                    }
                }

            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }

        }

        #endregion Import Excel

        


        #region Excel Pivot Data

        private object useDefault = Type.Missing;

        private string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

        /// <summary>
        /// Helper method to set a value on a single cell.
        /// </summary>
        private void SetCellValue(OfficeExcel.Worksheet targetSheet, string cell, object value)
        {
            targetSheet.get_Range(cell, useDefault).set_Value(OfficeExcel.XlRangeValueDataType.xlRangeValueDefault, value);
        }


        private void ExcelPivotPayment()
        {

            // Declare variables that hold references to excel objects
            OfficeExcel.Application excelApplication = null;
            OfficeExcel.Workbook excelWorkBook = null;
            OfficeExcel.Worksheet targetSheet = null;
            OfficeExcel.Worksheet pivotSheet = null;
            OfficeExcel.PivotTable pivotTable = null;
            OfficeExcel.Range pivotData = null;
            OfficeExcel.Range pivotDestination = null;
            OfficeExcel.PivotField salesRegion = null;
            OfficeExcel.PivotField salesAmount = null;

            // Declare helper variables
            string workBookName = @"C:\Temp\PivotTableSample.xlsx";
            string pivotTableName = @"Payment Pivot Report";
            string workSheetName = @"Payment Journal";

            try
            {
                DataTable tableQuotaMasterJournal = this.quotaBLL.GetQuotaListingDetail();

                if (tableQuotaMasterJournal.Rows.Count <= 0) throw new Exception("There is no data to show.");






                object[,] arr = new object[tableQuotaMasterJournal.Rows.Count, tableQuotaMasterJournal.Columns.Count];
                for (int rowIndex = 0; rowIndex < tableQuotaMasterJournal.Rows.Count; rowIndex++)
                {
                    for (int columnIndex = 0; columnIndex < tableQuotaMasterJournal.Columns.Count; columnIndex++)
                    {
                        arr[rowIndex, columnIndex] = (tableQuotaMasterJournal.Columns[columnIndex].ColumnName == "PaymentPeriod" ? "'" : "") + tableQuotaMasterJournal.Rows[rowIndex][columnIndex];
                    }
                }




                // Create an instance of Excel
                excelApplication = new OfficeExcel.Application();
                //excelApplication.Visible = true;//Le Minh Hiep

                //Create a workbook and add a worksheet.
                excelWorkBook = excelApplication.Workbooks.Add(OfficeExcel.XlWBATemplate.xlWBATWorksheet);
                targetSheet = (OfficeExcel.Worksheet)(excelWorkBook.Worksheets[1]);
                targetSheet.Name = "Filter Description";
                var startCell = (OfficeExcel.Range)targetSheet.Cells[1, 1];
                var endCell = (OfficeExcel.Range)targetSheet.Cells[1, 2];
                var writeRange = targetSheet.Range[startCell, endCell];

                writeRange.Value2 = this.quotaBLL.FilterDescription();
                targetSheet.get_Range("A1", "B1").Columns.AutoFit();
                targetSheet.get_Range("A1", "B1").Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(221, 217, 195));


                excelWorkBook.Worksheets.Add(useDefault, targetSheet);
                targetSheet = (OfficeExcel.Worksheet)(excelWorkBook.Worksheets[2]);
                targetSheet.Name = workSheetName;


                for (int columnIndex = 0; columnIndex < tableQuotaMasterJournal.Columns.Count; columnIndex++)
                {
                    this.SetCellValue(targetSheet, this.GetExcelColumnName(columnIndex + 1) + "1", tableQuotaMasterJournal.Columns[columnIndex].ColumnName);
                }



                startCell = (OfficeExcel.Range)targetSheet.Cells[2, 1];
                endCell = (OfficeExcel.Range)targetSheet.Cells[tableQuotaMasterJournal.Rows.Count + 1, tableQuotaMasterJournal.Columns.Count];
                writeRange = targetSheet.Range[startCell, endCell];

                writeRange.Value2 = arr;





                for (int columnIndex = 0; columnIndex < tableQuotaMasterJournal.Columns.Count; columnIndex++)
                {
                    if (tableQuotaMasterJournal.Columns[columnIndex].DataType == typeof(System.DateTime) || tableQuotaMasterJournal.Columns[columnIndex].DataType == typeof(System.Double))
                        ((OfficeExcel.Range)targetSheet.Cells[2, columnIndex + 1]).EntireColumn.NumberFormat = tableQuotaMasterJournal.Columns[columnIndex].DataType == typeof(System.DateTime) ? "dd/mm/yyyy" : "#,###";


                }


                targetSheet.get_Range("A1", this.GetExcelColumnName(tableQuotaMasterJournal.Columns.Count) + (tableQuotaMasterJournal.Rows.Count + 1).ToString()).Columns.AutoFit();
                targetSheet.get_Range("A1", this.GetExcelColumnName(tableQuotaMasterJournal.Columns.Count) + "1").Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(197, 190, 151));
                targetSheet.get_Range("A1", this.GetExcelColumnName(tableQuotaMasterJournal.Columns.Count) + "1").RowHeight = targetSheet.get_Range("A1", this.GetExcelColumnName(tableQuotaMasterJournal.Columns.Count) + "1").RowHeight + 8;
                targetSheet.get_Range("A1", this.GetExcelColumnName(tableQuotaMasterJournal.Columns.Count) + "1").VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter;



                // Select a range of data for the Pivot Table.
                pivotData = targetSheet.get_Range("A1", this.GetExcelColumnName(tableQuotaMasterJournal.Columns.Count) + (tableQuotaMasterJournal.Rows.Count + 1).ToString());

                // Select location of the Pivot Table.
                excelWorkBook.Worksheets.Add(useDefault, targetSheet);//OfficeExcel.XlWBATemplate.xlWBATWorksheet);
                pivotSheet = (OfficeExcel.Worksheet)(excelWorkBook.Worksheets[3]);
                pivotSheet.Name = "Pivot Report";
                pivotSheet.Activate();
                pivotDestination = pivotSheet.get_Range("A1", useDefault);

                // Add a pivot table to the work sheet.
                excelWorkBook.PivotTableWizard(
                    OfficeExcel.XlPivotTableSourceType.xlDatabase,
                    pivotData,
                    pivotDestination,
                    pivotTableName,
                    true,
                    true,
                    true,
                    true,
                    useDefault,
                    useDefault,
                    false,
                    false,
                    OfficeExcel.XlOrder.xlDownThenOver,
                    0,
                    useDefault,
                    useDefault
                    );

                // Set variables for used to manipulate the Pivot Table.
                pivotTable =
                    (OfficeExcel.PivotTable)pivotSheet.PivotTables(pivotTableName);

                salesAmount = ((OfficeExcel.PivotField)pivotTable.PivotFields(13));

                // Format the Pivot Table.
                pivotTable.Format(OfficeExcel.XlPivotFormatType.xlPTClassic);
                pivotTable.InGridDropZones = false;


                ////Set column field
                //OfficeExcel.PivotField fld = ((OfficeExcel.PivotField)pivotTable.PivotFields("Reference"));
                //fld.Orientation = OfficeExcel.XlPivotFieldOrientation.xlDataField;
                //fld.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
                //fld.NumberFormat = "#,##0_);[Red](#,##0)";


                OfficeExcel.PivotField fld = ((OfficeExcel.PivotField)pivotTable.PivotFields("PaymentTerm")); // Set a column field.
                fld.Orientation = OfficeExcel.XlPivotFieldOrientation.xlColumnField;


                fld = ((OfficeExcel.PivotField)pivotTable.PivotFields("PaymentType")); // Set a row field.
                fld.Orientation = OfficeExcel.XlPivotFieldOrientation.xlRowField;


                fld = ((OfficeExcel.PivotField)pivotTable.PivotFields("PaymentMonth")); // Set a row field.
                fld.Orientation = OfficeExcel.XlPivotFieldOrientation.xlRowField;

                fld = ((OfficeExcel.PivotField)pivotTable.PivotFields("PaymentDate")); // Set a row field.
                fld.Orientation = OfficeExcel.XlPivotFieldOrientation.xlRowField;

                fld = ((OfficeExcel.PivotField)pivotTable.PivotFields("PaymentAmount")); // Set a value field.
                fld.Orientation = OfficeExcel.XlPivotFieldOrientation.xlDataField;
                fld.Function = OfficeExcel.XlConsolidationFunction.xlSum;
                fld.NumberFormat = "#,###";

                //// Set Sum of Sales Amount as a Value Field.
                //salesAmount.Orientation =
                //    OfficeExcel.XlPivotFieldOrientation.xlDataField;
                //salesAmount.Function = OfficeExcel.XlConsolidationFunction.xlSum;

                //// Save the workbook.
                //excelWorkBook.SaveAs(workBookName, useDefault, useDefault,
                //    useDefault, useDefault, useDefault,
                //    XlSaveAsAccessMode.xlNoChange, useDefault, useDefault,
                //    useDefault, useDefault, useDefault);   //Le Minh Hiep

                excelApplication.Visible = true;//Le Minh Hiep
            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
            finally
            {
                // Release the references to the Excel objects.
                salesAmount = null;
                salesRegion = null;
                pivotDestination = null;
                pivotData = null;
                pivotTable = null;
                targetSheet = null;

                // Release the Workbook object.
                if (excelWorkBook != null)
                    excelWorkBook = null;

                //// Release the ApplicationClass object.
                //if (excelApplication != null)
                //{
                //    excelApplication.Quit();
                //    excelApplication = null;
                //}//Le Minh Hiep

                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }



        #endregion Excel Pivot


        private void toolStripButtonPivotPayment_Click(object sender, EventArgs e)
        {
            this.ExcelPivotPayment();
        }

        private void comboBoxMonthID_SelectedIndexChanged(object sender, EventArgs e)
        {

            for (int i = 1; i <= 12; i++)
            {
                if (i != this.comboBoxMonthID.SelectedIndex + 1)
                {
                    this.dataGridViewQuotaDetail.Columns["QuantityQuota" + i.ToString("00")].Visible = false;
                    this.dataGridViewQuotaDetail.Columns["AmountQuota" + i.ToString("00")].Visible = false;
                }
                else
                {
                    this.dataGridViewQuotaDetail.Columns["QuantityQuota" + i.ToString("00")].Visible = true;
                    this.dataGridViewQuotaDetail.Columns["AmountQuota" + i.ToString("00")].Visible = true;
                }
            }      
           

        }


        private void dataListViewMaster_DoubleClick(object sender, EventArgs e)
        {
            this.naviGroupDetails.Expanded = true;
        }

        private void dataGridViewQuotaDetail_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            for (int i = 1; i <= 12; i++)
            {
                if (dataGridViewQuotaDetail.Columns[e.ColumnIndex].DataPropertyName == "QuantityQuota" + i.ToString("00"))
                {
                    if (e.Value != null)
                    {
                        double value = (double)e.Value;
                        if (value == 0)
                        {
                            e.Value = string.Empty;
                            e.FormattingApplied = true;
                        }
                    }
                }

                if (dataGridViewQuotaDetail.Columns[e.ColumnIndex].DataPropertyName == "AmountQuota" + i.ToString("00"))
                {
                    if (e.Value != null)
                    {
                        double value = (double)e.Value;
                        if (value == 0)
                        {
                            e.Value = string.Empty;
                            e.FormattingApplied = true;
                        }
                    }
                }
            }
        }

     


    }




}
